package com.yellowpage.bigdata.service;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.collections.map.LinkedMap;

import com.opensymphony.xwork2.ActionContext;
import com.yellowpage.bigdata.dao.NspFatallogImeiDayregionSumDao;
import com.yellowpage.bigdata.data.NspFatallogImeiDayregionSum;
import com.yellowpage.ca.dao.RegionDao;
import com.yellowpage.ca.data.User;
import com.yellowpage.ca.service.RegionService;
import com.yellowpage.white.common.PageRender;
import com.yellowpage.white.dao.QueryAssistantSQL;
import com.yellowpage.white.service.BaseServiceImpl;

public class NspFatallogImeiDayregionSumServiceImpl extends BaseServiceImpl
		implements NspFatallogImeiDayregionSumService {
	private NspFatallogImeiDayregionSumDao nspFatallogImeiDayregionSumDao;
	
	public void setNspFatallogImeiDayregionSumDao(
			NspFatallogImeiDayregionSumDao nspFatallogImeiDayregionSumDao) {
		this.nspFatallogImeiDayregionSumDao = nspFatallogImeiDayregionSumDao;
	}

	/* Smooth generate Method */
	public NspFatallogImeiDayregionSum getNspFatallogImeiDayregionSum(Long id) {
		return nspFatallogImeiDayregionSumDao.get(id);
	}

	public List<NspFatallogImeiDayregionSum> getAllNspFatallogImeiDayregionSum() {
		return nspFatallogImeiDayregionSumDao.getAll();
	}
    
    


	
	public List<Map> queryNspFatallogImeiDayregionSumByPage(Map queryMap,
			PageRender pageRender) {
		QueryAssistantSQL sqlAssistant = baseDao
				.newQueryAssistantSQL(
						"SELECT a.FATALLOG_IMEI_DAYREGION_SUM_ID , a.NEXT_DAY_RETAINED,a.THREE_DAY_RETAINED,a.SEVEN_DAY_RETAINED,a.THIRTY_DAY_RETAINED, "
								+ "(select c.COMPANY from NSP_REQUEST_TYPE c where c.CODE = a.REQUEST_TYPE) as REQUEST_TYPE , "
								+ "a.IMEI_DAY_COUNT , a.IMEI_ALL_COUNT , a.IMEI_NEW_COUNT , "
								+ "a.REGION_CODE , a.CREATE_USER , a.LOG_DATE , a.CREATE_TIME , a.NOTE,"
								+ "(select c.name from OMC_REGION c where c.REGION_CODE=a.region_code) as REGION_NAME "
								+ " FROM NSP_FATALLOG_IMEI_DAYREGION_SUM a WHERE REQUEST_TYPE<>'A' and REQUEST_TYPE not in(select `code` from NSP_CHANNEL) ")
				.addCondition(" and REQUEST_TYPE = :requestType ")
				.addCondition(" and LOG_DATE >= :logDate ")
				.addCondition(" and LOG_DATE <= :query_logDate_end ")
				.addCondition(" and REGION_CODE = :regionCode ");

		List<Map> resultList = sqlAssistant.queryByPage(
				baseDao.getConnection(), queryMap, pageRender);

		return resultList;
	}

	@Override
	public Map getrequestTypeMap() {
		Map dataSourceMap = new LinkedMap();
		QueryAssistantSQL sqlAssistant = baseDao
				.newQueryAssistantSQL(" SELECT a.COMPANY as NAME, a.CODE as VALUE  FROM NSP_REQUEST_TYPE a");
		sqlAssistant.setTail("order by (a.CODE+0)");
		List<Map> resultList = sqlAssistant.query(baseDao.getConnection(),
				new HashMap());
		for (int i = 0; i < resultList.size(); i++) {
			dataSourceMap.put(resultList.get(i).get("VALUE"), resultList.get(i)
					.get("VALUE") + "-" + resultList.get(i).get("NAME"));
		}
		return dataSourceMap;
	}

	@Override
	public List<Map> queryregion(Map queryMap) {
		QueryAssistantSQL sqlAssistant = baseDao.newQueryAssistantSQL(
				"SELECT c.REGION_CODE FROM OMC_REGION c WHERE 1=1")
				.addCondition(" and c.NAME like :regionCode ");

		List<Map> resultList = sqlAssistant.query(baseDao.getConnection(),
				queryMap);

		return resultList;
	}
	
	@Override
	public Map<String,String> getRegionMap(String regionSql ) {
		Map<String,String> dataSourceMap = new LinkedMap();
//		QueryAssistantSQL sqlAssistant = baseDao
//				.newQueryAssistantSQL(" select a.REGION_CODE as NAME, a.SHORT_NAME as VALUE from OMC_REGION a where a.REGION_CODE like'%0000'");
		QueryAssistantSQL sqlAssistant = baseDao
				.newQueryAssistantSQL(" select a.REGION_CODE as NAME, a.SHORT_NAME as VALUE from OMC_REGION a where 1 = 1 "+ regionSql);
		List<Map> resultList = sqlAssistant.query(baseDao.getConnection(),
				new HashMap());
		for (int i = 0; i < resultList.size(); i++) {
			dataSourceMap.put(resultList.get(i).get("NAME").toString(), resultList.get(i).get("VALUE").toString());
		}
		return dataSourceMap;
	}
	
 	@Override
	public Map<String,String> getChannelMap() {
		Map<String,String> dataSourceMap = new LinkedMap();
		QueryAssistantSQL sqlAssistant = baseDao
				.newQueryAssistantSQL(" SELECT a.code as NAME, a.name as VALUE  FROM NSP_CHANNEL a  ");
		List<Map> resultList = sqlAssistant.query(baseDao.getConnection(),
				new HashMap());
		for (int i = 0; i < resultList.size(); i++) {
			dataSourceMap.put(resultList.get(i).get("NAME").toString(), resultList.get(i).get("VALUE").toString());
		}
		return dataSourceMap;
	}

	@Override
	public List<Map> queryNspFatallogDayregionCountByPage(Map queryMap,
			PageRender pageRender) {
		String t2 = queryMap.get("regionCode").equals("") ? "" : " and SU.REGION_CODE in "+queryMap.get("regionCode");
		QueryAssistantSQL sqlAssistant = baseDao
				.newQueryAssistantSQL(
						"select SU.LOG_DATE,SU.REQUEST_TYPE,(select c.COMPANY from NSP_REQUEST_TYPE c where c.CODE = SU.REQUEST_TYPE) as REQUEST_TYPE_NAME,"
								+ "(select c.name from OMC_REGION c where c.REGION_CODE=SU.region_code) as region_name,"
								+ "sum(SU.a) as REQUEST_COUNT, sum(SU.imei) as IMEI_REQUEST_COUNT, sum(SU.noimei) as NO_IMEI_REQUEST_COUNT,"
								+ "X.imei_day_count, X.imei_all_count, X.imei_new_count "
								+ "from (select c.LOG_DATE, c.request_type, c.REGION_CODE,"
								+ "if(data_flag='A', sum(request_count),0 ) as a,"
								+ "if(data_flag='IMEI', sum(request_count),0 ) as imei,"
								+ "if(data_flag='NOIMEI', sum(request_count),0 ) as noimei "
								+ "from NSP_FATALLOG_REQUSET_SUM c  "
								+ "GROUP BY  c.LOG_DATE, c.request_type, c.REGION_CODE, c.data_flag) SU "
								+ "left JOIN NSP_FATALLOG_IMEI_DAYREGION_SUM X on SU.LOG_DATE = X.LOG_DATE and SU.request_type = X.request_type "
								+ "and SU.REGION_CODE = X.REGION_CODE "
								//+ "and X.region_code='A'"
								+ " where (SU.REQUEST_TYPE='X' OR SU.REQUEST_TYPE not in(select `code` from NSP_CHANNEL)) "+t2)
				.addCondition(" and SU.LOG_DATE >= :logDate ")
				.addCondition(" and SU.LOG_DATE <= :query_logDate_end ")
				.addCondition(" and SU.REQUEST_TYPE = :requestType ");
//				.addCondition(" and SU.REGION_CODE = :regionCode ");
		sqlAssistant
				.setTail(" group by SU.LOG_DATE, SU.request_type,SU.REGION_CODE");
		List<Map> resultList = sqlAssistant.queryByPage(
				baseDao.getConnection(), queryMap, pageRender);

		return resultList;
	}

	@Override
	public Map getChannelTypeMap() {
		Map dataSourceMap = new LinkedMap();
		QueryAssistantSQL sqlAssistant = baseDao
				.newQueryAssistantSQL(" SELECT a.name as NAME, a.CODE as VALUE  FROM NSP_CHANNEL a  ");
		sqlAssistant.setTail("order by (a.CODE+0)");
		List<Map> resultList = sqlAssistant.query(baseDao.getConnection(),
				new HashMap());
		for (int i = 0; i < resultList.size(); i++) {
			dataSourceMap.put(resultList.get(i).get("VALUE"), resultList.get(i)
					.get("VALUE") + "-" + resultList.get(i).get("NAME"));
		}
		return dataSourceMap;
	}
	
	@Override
	public List<Map> queryNspFatallogDayRegionChannlByPage(Map queryMap,
			PageRender pageRender){
	    
		
		String t1 = queryMap.get("channelCode").equals("") ? "" : " and  c.code in ( "+queryMap.get("channelCode")+") ";
		String t2 = queryMap.get("regionCode").equals("") ? "" : "  and region_code in ( "+queryMap.get("regionCode")+") ";
		 
		String t3 = queryMap.get("query_logDate_end").equals("") ? "" : " and c.LOG_DATE <= '"+queryMap.get("query_logDate_end")+"'" ;
		String t4 = queryMap.get("logDate").equals("") ? "" : " and c.LOG_DATE >= '"+queryMap.get("logDate")+"'" ;
		System.out.println("----t1:"+t1);
		System.out.println("----t2:"+t2);
		System.out.println("----t3:"+t3);
		System.out.println("----t4:"+t4);
		QueryAssistantSQL sqlAssistant = baseDao
				.newQueryAssistantSQL(
						"     select      su.log_date,"
								+"                               d.name   as region_name,"
								+"								 c.name   as request_type,"
								+"								 x.imei_day_count, x.imei_all_count, x.imei_new_count ,"
								+"								 sum(su.a) as request_count, sum(su.imei) as imei_request_count, sum(su.noimei) as no_imei_request_count 	"						
								+"    from        (select c.log_date, c.request_type, c.region_code,"
								+"								if(data_flag='a', sum(request_count),0 ) as a,"
								+"								if(data_flag='imei', sum(request_count),0 ) as imei,"
								+"								if(data_flag='noimei', sum(request_count),0 ) as noimei "
								+"								from NSP_FATALLOG_REQUSET_SUM c where 1=1    " + t3 + t4
								+"								group by  c.log_date, c.request_type, c.region_code, c.data_flag) su "
								+ "   inner join  ( select * from  NSP_CHANNEL c  where  1=1      "  + t1  + " ) c "        
								+ "   on c.code = su.request_type	  "
								+ "   inner join ( select * from  OMC_REGION d  where 1=1       " + t2 + " ) d "
								+ "   on d.region_code=su.region_code "
								+ "   left join   NSP_FATALLOG_IMEI_DAYREGION_SUM x "
								+ "   on su.log_date = x.log_date            "
								+ "	  and su.request_type = x.request_type   "
								+ "	  and su.region_code = x.region_code	 "
								+ "   where 1=1 ")   
								        .addCondition(" and su.LOG_DATE >= :logDate ")
										.addCondition(" and su.LOG_DATE <= :query_logDate_end ")  ;						 
//									    .addCondition(" and su.REQUEST_TYPE in (  :requestType ) ")
//									    .addCondition(" and su.region_code in ( :regionCode ) ");
								sqlAssistant.setTail(" group by su.log_date, su.request_type,su.region_code  ");
 

		List<Map> resultList = sqlAssistant.queryByPage(
				baseDao.getConnection(), queryMap, pageRender);
		return  resultList;
	}
	
	public void saveNspFatallogImeiDayregionSum(
			NspFatallogImeiDayregionSum nspFatallogImeiDayregionSum) {
		nspFatallogImeiDayregionSumDao.save(nspFatallogImeiDayregionSum);
	}

	public void updateNspFatallogImeiDayregionSum(
			NspFatallogImeiDayregionSum nspFatallogImeiDayregionSum) {
		nspFatallogImeiDayregionSumDao.update(nspFatallogImeiDayregionSum);
	}

	public void delete(List deleteList) {
		nspFatallogImeiDayregionSumDao.remove(deleteList);
	}

}